#!/usr/bin/python
# -*- coding: utf-8 -*-
# author: Miguel Nieto

import sqlite3, os
from bottle import route, run, debug, template, request, error, send_file

@route('/machinelist')
def machine_list():
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    c.execute("SELECT name FROM machines ORDER BY name ASC")
    nam = c.fetchall() # lista de maquinas
    c.execute("SELECT tag FROM tags ORDER BY tag ASC")
    tag = c.fetchall() # lista de etiquetas
    c.close()
    names=list()
    tags=list()
    
    for n in nam:
        na = n[0].encode('utf-8')
        if na not in names:
            names.append(na)
    for t in tag:
        ta = t[0].encode('utf-8')
        if ta not in tags:
            tags.append(ta)
    
    return template('machine_list.tpl', names=names, tags=tags)

@route('/show/:na')
@route('/show/:na/')
def show_item_name(na):
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    
    '''extracción individual de los datos de la base de datos
    '''
    query="SELECT id FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query)
    id = c.fetchall() 
    query="SELECT name FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query)
    nam = c.fetchall()
    query="SELECT author FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query)
    auth = c.fetchall()
    query="SELECT description FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query)
    desc = c.fetchall()
    query="SELECT version FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query) 
    vers = c.fetchall()
    query="SELECT dependencies FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query) 
    depend = c.fetchall()
    query="SELECT nature FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query) 
    nature = c.fetchall()
    query="SELECT application FROM machines WHERE name LIKE '%s' ORDER BY id ASC" %na
    c.execute(query) 
    applic = c.fetchall()
    c.close()    
    
    ids = list()
    for i in id:
        ids.append(i)
    ''' se crea un diccionario que contendrá un diccionario, o más
        en caso de que haya varias máquinas con el mismo nombre, un
        diccionario por cada máquina.
    '''
    files = dict()
    if ids: #si hay algún elemento
        for j in range(len(ids)):
            file = dict()
            file['id']=ids[j][0]
            file['name'] = nam[j][0].encode('utf-8')
            file['author'] = auth[j][0].encode('utf-8')
            file['description'] = desc[j][0].encode('utf-8')
            
            '''etiquetas extraídas de las entradas correspondientes de la tabla 'tags'
            '''
            tags = list()
            c.execute("SELECT tag FROM tags WHERE idtag LIKE %s ORDER BY tag ASC" %file['id'] )
            tlist = c.fetchall() # lista de etiquetas
            for t in tlist:
                ta = t[0].encode('utf-8')
                if ta not in tags:
                    tags.append(ta)
                
            file['tags'] = tags
            file['version'] = vers[j][0].encode('utf-8')
            file['dependencies'] = depend[j][0].encode('utf-8')
            file['nature'] = nature[j][0].encode('utf-8')
            file['application'] = applic[j][0].encode('utf-8')
            files[j] = file
    
        return template('machine.tpl', files=files, mach=na)
    else:
        return template('unknown_machine.tpl', desc=na)
    
@route('/show/:na/:ve')
def show_item_name_n_version(na,ve):
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    
    '''extracción individual de los datos de la base de datos
    '''
    query="SELECT id FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query)
    id = c.fetchall() 
    query="SELECT name FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query)
    nam = c.fetchall()
    query="SELECT author FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query)
    auth = c.fetchall()
    query="SELECT description FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query)
    desc = c.fetchall()
    query="SELECT version FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query) 
    vers = c.fetchall()
    query="SELECT dependencies FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query) 
    depend = c.fetchall()
    query="SELECT nature FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query) 
    nature = c.fetchall()
    query="SELECT application FROM machines WHERE name LIKE '%s' AND version like '%s' ORDER BY id ASC" %(na,ve)
    c.execute(query) 
    applic = c.fetchall()
    c.close()    
    
    ids = list()
    for i in id:
        ids.append(i)
    ''' se crea un diccionario que contendrá un diccionario, o más
        en caso de que haya varias máquinas con el mismo nombre, un
        diccionario por cada máquina.
    '''
    files = dict()
    if ids: #si hay algún elemento
        for j in range(len(ids)):
            file = dict()
            file['id']=ids[j][0]
            file['name'] = nam[j][0].encode('utf-8')
            file['author'] = auth[j][0].encode('utf-8')
            file['description'] = desc[j][0].encode('utf-8')
            
            '''etiquetas extraídas de las entradas correspondientes de la tabla 'tags'
            '''
            tags = list()
            c.execute("SELECT tag FROM tags WHERE idtag LIKE %s ORDER BY tag ASC" %file['id'] )
            tlist = c.fetchall() # lista de etiquetas
            for t in tlist:
                ta = t[0].encode('utf-8')
                if ta not in tags:
                    tags.append(ta)
                
            file['tags'] = tags
            file['version'] = vers[j][0].encode('utf-8')
            file['dependencies'] = depend[j][0].encode('utf-8')
            file['nature'] = nature[j][0].encode('utf-8')
            file['application'] = applic[j][0].encode('utf-8')
            files[j] = file
    
        return template('machine.tpl', files=files, mach=na)
    else:
        return template('unknown_machine.tpl', desc=na, version=ve)
    
@route('/upload')
def upload():
    return template('upload_machine.tpl')

@route('/uploadcheck', method='POST')
def upload_check():    
    datafile = request.POST['echfile']
    try:#en Internet Explorer el nombre de archivo incluye la ruta completa
        #por ello se intenta extraer \\
        filename = datafile.filename.rpartition('\\')[-1]
    except AttributeError:
        return template('upload_unknown_file.tpl')
    '''extracción de la confirmación según la cual el archivo se insertará o no en la base de datos'''
    try:
        process = request.POST['process']
    except KeyError:
        process = ''    
    sfilename = filename.rpartition('.') #nombre de archivo separado de la extensión

    '''proceso de extracción del contenido del archivo adaptado a las características de la información
    recogida mediante POST '''
    if sfilename[2] == 'ech':
        datastr = datafile.value
        
        try: #se extrae la información de a qué paquete pertenece el archivo
            path = datastr.split('package ')[-1].split(';', 1)[0].replace('.', '/') + '/'
        except IndexError:
            path = ''
        try:
            os.makedirs('../files/' + path)
        except:
            pass        
        tempfile = open('../files/' + path + filename, 'w')
        tempfile.write(datastr)
        tempfile.close()
        executor = 'python ../bin/ech2dot --target-directory ../files --echartspath ../files/ '
        executor += '../files/' + path + filename        
        os.system(executor)
        ferr = open('../files/machineNotFound.log', 'r')
        err = ferr.read()
        ferr.close()
        #si el usuario desea que se procese toda la información y se genere el diagrama:
        if process:
            conn = sqlite3.connect('machines.db')
            c = conn.cursor()
        
            try:
                name = datastr.split('@name ')[1].split('\n', 1)[0].strip(' ')
                if name != sfilename[0]:
                    raise IndexError
            except IndexError:
                return template('upload_machine_error.tpl')
            try:
                author = datastr.split('@author ')[1].split('\n',1)[0].strip(' ')
            except IndexError:
                author = ''
    
            '''sucesivas sustituciones de cadenas en la descripción
            para eliminar asteriscos de comentario Java'''
    
            description = datastr.split('@description ')[1].split('@description')[0]
            description = description.replace('\n','<br/>').replace('<br/> *', '<br/>').strip(' ')
            '''eliminación de espacios innecesarios tras el salto de línea.
            el bucle nunca será infinito ya que en caso de encontrar la cadena,
            la acción que se realiza es justo eliminarla de la descripción'''
            while description.find('<br/> ') != -1:
                description = description.replace('<br/> ', '<br/>')
    
            try:
                tags = datastr.split('@tags ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                tags = ''
            try:
                version = datastr.split('@version ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                version = ''
            try:
                dependencies = datastr.split('@dependencies ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                dependencies = ''
            try:
                nature = datastr.split('@nature ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                nature = ''
            try:
                application = datastr.split('@application ')[1].split('\n', 1)[0].strip(' ')
            except IndexError:
                application = ''
    
            query = '''INSERT INTO machines (name,author,description,tags,version,dependencies,nature,application) 
                    VALUES ('%s','%s','%s','%s','%s','%s','%s','%s')''' % (name, author, description, tags,
                                                                           version, dependencies, nature, application)

            c.execute(query)
            conn.commit()
            '''una vez insertada la máquina en la tabla machines,
            se insertan sus etiquetas coherentemente'''
            c.execute("SELECT last_insert_rowid()")
            new_id = c.fetchone()[0]

            query="SELECT tags FROM machines WHERE id LIKE '%s'" %new_id
            c.execute(query)
            tags = c.fetchone()[0].encode('utf-8')
            tlist = tags.split(';')
            for t in tlist:
                query="INSERT INTO tags (idtag, tag) VALUES (%s, '%s')" %(new_id,t.strip(' ')) 
                try:
                    c.execute(query)
                except sqlite3.IntegrityError:
                    pass
                conn.commit()
            c.close()
            #generación de los diagramas de flujo
            executor = 'dot -Tpng -Efontsize=12 -Elabelfontsize=8 -Gfontsize=12 -Gsize="10.5,7" -o ../images/'
            executor += sfilename[0] + version + '.png ../files/' + path + sfilename[0] + '.dot'
            os.system(executor)
                
            return template('check_upload.tpl', name=name, author=author, version=version,
                            description=description, dependencies=dependencies, nature=nature,
                            tlist=tlist, application=application, id=new_id, err=err)  
        else:
            return template('not_processed_upload.tpl', filename=filename, err=err)
    else:
        return template('wrong_file.tpl', filename=filename)

#TODO: realizar la búsqueda por etiquetas
@route('/showtag/:ta')
def find_tag(ta):
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    query = "SELECT idtag FROM tags WHERE tag like '%s'" %ta 
    c.execute(query)
    ids = c.fetchall()
    mach = dict()
    for id in ids:
        query = "SELECT name FROM machines WHERE id LIKE %s" %id[0]
        c.execute(query)
        name = c.fetchone()[0].encode('utf-8')
        query = "SELECT version FROM machines WHERE id LIKE %s" %id[0]
        c.execute(query)
        version = c.fetchone()[0].encode('utf-8')
        mach[name] = version
    
    c.close()
    return template('show_tag.tpl', machines=mach, tag=ta)

@route('/delmachine/:id')
def delete_machine(id):
    conn = sqlite3.connect('machines.db')
    c = conn.cursor()
    query = "DELETE FROM machines WHERE id LIKE %s" %id
    c.execute(query)
    conn.commit()
    query = "DELETE FROM tags WHERE idtag LIKE %s" %id
    c.execute(query)
    conn.commit()
    c.close()
    return template('deleted_machine.tpl', id=id)

@route('/image/:ima#.*\.png#')
def static_image(ima):
    send_file(ima, root='../images', mimetype='image/png')


@error(403)
def mistake403(code):
    return 'El par&aacute;metro que ha pasado tiene el formato incorrecto'
@error(404)
def mistake404(code):
    return 'Lo siento, la p&aacute;gina no se encuentra'

debug(False) #True para debugging
run(reloader=False) #True para que se actualicen los cambios sin tener que reiniciar manualmente